package jnpf.util;

import com.google.common.base.Joiner;
import jnpf.database.source.DbBase;
import jnpf.database.util.DbUtil;
import lombok.Cleanup;
import lombok.extern.slf4j.Slf4j;
import java.sql.*;
import java.util.*;
import java.util.concurrent.*;


/**
 * @author JNPF开发平台组
 * @version V3.1.0
 * @copyright 引迈信息技术有限公司
 * @date 2021/3/16 8:49
 */
@Slf4j
public class JdbcUtil {
    //-----------------------------jdbc工具类----------------------------------------------------
    /**
     * 自定义sql语句(适合增、删、改)
     * 输入的语句需要全小写，除了数据
     */
    public static int custom(Connection conn, String sql) {
        int result = 0;
        try {
            String dbType = conn.getMetaData().getDatabaseProductName().trim().toLowerCase();
            //TODO这里要修改
            if ("oracle".equals(dbType)) {
                //关闭事务自动提交
                conn.setAutoCommit(false);
                String[] sqls = sql.split(";");
                @Cleanup PreparedStatement preparedStatement = null;
                for (String sqlOne : sqls) {
                    if (sqlOne.toLowerCase().contains("insert") && sqlOne.replaceAll(" ", "").contains("),(")) {
                        String[] splitSql = sqlOne.split("\\),\\(");
                        //centerSql取出INTO TEST_DETAILS ( F_ID, F_RECEIVABLEID)
                        String centerSql = splitSql[0].split("VALUES")[0].split("INSERT")[1];
                        //for循环尾部
                        String lastSql = splitSql[splitSql.length - 1];
                        splitSql[splitSql.length - 1] = lastSql.substring(0, lastSql.length() - 1);
                        for (int i = 0; i < splitSql.length; i++) {
                            //第一个语句INSERT INTO TEST_DETAILS ( F_ID, F_RECEIVABLEID) VALUES ( '71', '28bf3436e5d1'
                            //需要拼接成 INSERT INTO TEST_DETAILS ( F_ID, F_RECEIVABLEID) VALUES ( '71', '28bf3436e5d1'）
                            if (i == 0) {
                                System.out.println(splitSql[i] + ")");
                                preparedStatement = conn.prepareStatement(splitSql[i] + ")");
                                preparedStatement.execute();
                                preparedStatement.close();
                            } else {
                                preparedStatement = conn.prepareStatement("INSERT " + centerSql + "VALUES (" + splitSql[i] + ")");
                                System.out.println("INSERT " + centerSql + "VALUES (" + splitSql[i] + ")");
                                preparedStatement.execute();
                                preparedStatement.close();
                            }
                        }
                        result++;
                    } else {
                        preparedStatement = conn.prepareStatement(sqlOne);
                        preparedStatement.executeUpdate();
                        result++;
                    }
                }
            } else {
                //开启事务
                conn.setAutoCommit(false);
                String[] sqls = sql.split(";");
                PreparedStatement preparedStatement;
                for (String sqlOne : sqls) {
                    try {
                        preparedStatement = conn.prepareStatement(sqlOne);
                        preparedStatement.executeUpdate();
                        result++;
                    }catch (Exception e){
                        e.getMessage();
                    }
                }
            }
            //提交事务
            conn.commit();
            return result;
        } catch (Exception e) {
            e.printStackTrace();
            log.error(e.getMessage());
            try {
                conn.rollback();
            } catch (SQLException q) {
                log.error("数据错误:" + q.getMessage());
            }
            return result;
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                log.error(e.getMessage());
            }
        }
    }


    /**
     * 自定义sql语句(查)
     */
    public static ResultSet query(Connection conn, String sql) {
        ResultSet resultSet = null;
        PreparedStatement preparedStatement;
        try {
            //开启事务
            conn.setAutoCommit(false);
            preparedStatement = conn.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            //提交事务
            conn.commit();
            return resultSet;
        } catch (Exception e) {
            log.error(e.getMessage());
        }
        return resultSet;
    }

    /**====================================================================**/

    /**
     * 连接Connection
     */
    public static Connection getConn(String dataType, String userName, String password, String host, String port, String dbName){
        //大小写不敏感
        DbBase db = DbUtil.getDbByEquals(dataType);
        String driverClass = db.getDriver();
        String url = db.getConnectionUrl(host,port,dbName);
        try {
            return connCommon(userName,password,url,driverClass,"204");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static Connection getConn(DbBase db,String userName, String password, String host, String port, String dbName) throws Exception{
        String driverClass = db.getDriver();
        String url = db.getConnectionUrl(host,port,dbName);
        return connCommon(userName,password,url,driverClass,"204");
    }

    public static Connection getConn(String userName, String password, String url,String driverClass) {
        try {
            return connCommon(userName,password,url,driverClass,"254");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static Connection getConn(String userName, String password, String url) {
        DbBase db = DbUtil.getDbByContains(url);
        String driverClass = db.getDriver();
        try {
            return connCommon(userName,password,url,driverClass,"254");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**====================================================================**/

    private static Connection connCommon(String userName, String password, String url, String driverClass,String warning) throws Exception{
        final Connection[] conn = {null};
        Callable<String> task = getTask(userName,password,url,driverClass,conn);
        futureGo(task,warning);
        return conn[0];
    }


    private static void futureGo(Callable<String> task,String warning) throws Exception{
        ExecutorService executorService = Executors.newSingleThreadExecutor();
        Future<String> future = executorService.submit(task);
        try {
            //设置超时时间
            String rst = future.get(3L, TimeUnit.SECONDS);
        } catch (TimeoutException e) {
            log.error("数据库连接超时,请检查数据连接信息。"+"error:"+e.getStackTrace());
            throw new Exception("连接数据库超时：请检查数据连接信息，或检查网络及数据库服务启动状态。");
        } catch (Exception e) {
            log.error("获取数据异常"+ warning +"," + e.getStackTrace());
            throw new Exception("获取数据异常"+ warning +"," + e.getStackTrace());
        } finally {
            executorService.shutdown();
        }
    }


    private static Callable<String> getTask(String userName, String password, String url, String driverClass, Connection[] conn){
        Callable<String> task = () -> {
            //执行耗时代码
            try {
                Class.forName(driverClass);
                conn[0] = DriverManager.getConnection(url, userName, password);
            } catch (Exception e) {
                log.error(e.getMessage());
            }
            return "jdbc连接成功";
        };
        return task;
    }



    //----------------------jdbc转成对象------------------

    /**
     * jdbc 多条数据查询转成list
     *
     * @param rs result 查询的结果
     * @return
     */
    public static List<Map<String, Object>> convertList(ResultSet rs) {
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
            while (rs.next()) {
                Map<String, Object> rowData = new HashMap<>(16);
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(md.getColumnName(i).toLowerCase(), rs.getString(i));
                }
                list.add(rowData);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return list;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                rs = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }



    //----------------------jdbc转成对象String------------------

    /**
     * jdbc 多条数据查询转成list
     *
     * @param rs result 查询的结果
     * @return
     */
    public static List<Map<String, Object>> convertListString(ResultSet rs) {
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
            while (rs.next()) {
                Map<String, Object> rowData = new HashMap<>(16);
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(md.getColumnName(i).toLowerCase(), rs.getString(i));
                }
                list.add(rowData);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return list;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                rs = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    /**
     * jdbc 单条数据查询转成map
     *
     * @param rs result 查询的结果
     * @return
     */
    public static Map<String, Object> convertMapString(ResultSet rs) {
        Map<String, Object> map = new TreeMap<>();
        try {
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
            while (rs.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    map.put(md.getColumnName(i), rs.getString(i));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return map;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                rs = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return map;
        }
    }

    /**
     * jdbc 多条数据查询转成list
     *
     * @param rs result 查询的结果，datetime转为时间戳
     * @return
     */
    public static List<Map<String, Object>> convertList2(ResultSet rs) {
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
            while (rs.next()) {
                Map<String, Object> rowData = new HashMap<>(16);
                for (int i = 1; i <= columnCount; i++) {
                    if (rs.getObject(i) != null) {
                        if (md.getColumnType(i) != 93) {
                            rowData.put(md.getColumnLabel(i), String.valueOf(rs.getObject(i)));
                        } else {
                            rowData.put(md.getColumnLabel(i), DateUtil.stringToDate(String.valueOf(rs.getObject(i))).getTime());
                        }
                    }
                }
                list.add(rowData);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return list;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                rs = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;

    }

    /**
     * 数据建模更新表字段
     */
    public static int upTableFields(Connection conn, String delSql, String crSql, String ramTable, String newTable) {
        int result = 0;
        try {
            //开启事务
            conn.setAutoCommit(false);
            PreparedStatement preparedStatement = conn.prepareStatement(crSql);
            preparedStatement.executeUpdate();
            //提交事务
            conn.commit();
            PreparedStatement preparedStatementdrop = conn.prepareStatement(delSql + "; alter table " + ramTable + " rename to " + newTable + ";");
            result = preparedStatementdrop.executeUpdate();
            //提交事务
            conn.commit();
            return result;
        } catch (Exception e) {
            log.error(e.getMessage());
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                log.error(e.getMessage());
            }
        }
        return result;
    }





    /**======================================废弃或未被使用================================================**/

    /**
     * jdbc 单条数据查询转成map
     *
     * @param rs result 查询的结果
     * @return
     */
    public static Map<String, Object> convertMap(ResultSet rs) {
        Map<String, Object> map = new TreeMap<>();
        try {
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
            while (rs.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    map.put(md.getColumnName(i), rs.getObject(i));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return map;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                rs = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return map;
        }
    }

    /**
     * 自定义sql语句(适合增、删、改)
     */
    public static int getCustom(Connection conn, String sql) {
        int result = 0;
        try {
            String dbType = conn.getMetaData().getDatabaseProductName().trim().toLowerCase();
            if ("oracle".equals(dbType)) {
                //关闭事务自动提交
                conn.setAutoCommit(false);
                String[] sqls = sql.split(";");
                PreparedStatement preparedStatement;
                for (String sqlOne : sqls) {
                    if (sqlOne.contains("INSERT") && sqlOne.trim().contains("),(")) {
                        List<String> chilSqls = new ArrayList<>();
                        String[] splitSql = sqlOne.split("\\),\\(");
                        //headerSql头sql
                        String headerSql = "INSERT ALL ";
                        //centerSql取出INTO TEST_DETAILS ( F_ID, F_RECEIVABLEID)
                        String centerSql = splitSql[0].split("VALUES")[0].split("INSERT")[1];
                        //for循环尾部
                        String lastSql = splitSql[splitSql.length - 1];
                        splitSql[splitSql.length - 1] = lastSql.substring(0, lastSql.length() - 1);
                        for (int i = 0; i < splitSql.length; i++) {
                            //第一个语句INSERT INTO TEST_DETAILS ( F_ID, F_RECEIVABLEID) VALUES ( '71', '28bf3436e5d1'
                            //需要拼接成 INTO TEST_DETAILS ( F_ID, F_RECEIVABLEID) VALUES ( '71', '28bf3436e5d1'）
                            if (i == 0) {
                                chilSqls.add("INTO " + splitSql[i].split("INTO")[1] + ")");
                            } else {
                                chilSqls.add(centerSql + "VALUES (" + splitSql[i] + ")");
                            }
                        }
                        String insertSql = headerSql + Joiner.on(" ").join(chilSqls) + " select 1 from DUAL";
                        System.out.println(insertSql);
                        preparedStatement = conn.prepareStatement(insertSql);
                        preparedStatement.executeUpdate();
                        result++;
                    } else {
                        preparedStatement = conn.prepareStatement(sqlOne);
                        preparedStatement.executeUpdate();
                        result++;
                    }
                }
            } else {
                //开启事务
                conn.setAutoCommit(false);
                String[] sqls = sql.split(";");
                PreparedStatement preparedStatement;
                for (String sqlOne : sqls) {
                    preparedStatement = conn.prepareStatement(sqlOne);
                    preparedStatement.executeUpdate();
                    result++;
                }
            }
            //提交事务
            conn.commit();
            return result;
        } catch (Exception e) {
            log.error(e.getMessage());
        }
        return result;
    }

    /**
     * 获取url
     *
     * @param userName 用户名
     * @param password 密码
     * @param host     ip地址
     * @param port     端口
     * @param sid      sid
     * @return
     */
    /*public static String getUrl(String dataType, String userName, String password, String host, String port, String dbName, String sid) {
        String connectionUrl = null;
        if (dataType.toLowerCase().equals(DbType.MYSQL.getDb())) {
            connectionUrl = "jdbc:mysql://" + host + ":" + port + "/" + dbName + "?useUnicode=true&autoReconnect=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8" + "." + userName + "." + password;
        } else if (dataType.toLowerCase().equals(DbType.ORACLE.getDb())) {
            connectionUrl = "jdbc:oracle:thin:@" + host + ":" + port + ":" + sid + "." + userName + "." + password;
        } else if (dataType.toLowerCase().equals(DbType.SQL_SERVER.getDb())) {
            connectionUrl = "jdbc:sqlserver://" + host + ":" + port + ";databaseName=" + dbName + "." + userName + "." + password;
        } else if (dataType.toLowerCase().equals(DbType.DM.getDb())) {
            connectionUrl = "jdbc:dm://" + host + ":" + port + "/" + dbName + "?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8" +
                    "." + userName + "." + password;
        }

        DbBase db = DbBase.getDbByEquals(dataType.toLowerCase());
        return db.getConnectionUrl(host,port,dbName);
    }*/




}
